DROP TABLE IF EXISTS dws.dws_trade_order_2020;
create table if not exists dws.dws_trade_order_2020(
orderid string, -- 订单id
`totalMoney` decimal, --总价
paytime string, -- 订单时间
quarters int, --季度
monthofyear int, --月份
dayofweek int, --星期
holiday int  /*工作日0 休息日1 法定假日2*/
) comment '2020 订单 金额 时间'
STORED AS PARQUET;

with tmp1 as (
    select 
    orderId,`totalMoney`,
    from_unixtime(unix_timestamp(payTime, 'yyyy-MM-dd HH:mm:ss'), 'yyyy-MM-dd') paytime
    from dwd.dwd_trade_orders
    where dt >='2019-12-15' and dt <= '2021-1-15' and end_date='9999-12-31'  and payTime>='2020-01-01' and payTime<='2020-12-31'
)
    insert overwrite table dws.dws_trade_order_2020
    select orderID, totalMoney, paytime,  quarter(paytime), month(paytime), dayofweek(paytime), parse_holiday(paytime) from tmp1